import pandas as pd
import numpy as np
import random
from datetime import datetime, timedelta

# 设置随机种子以确保可重复性
np.random.seed(42)

# 自定义数据生成函数
def random_name():
    first_names = ['张', '李', '王', '刘', '陈', '杨', '赵', '黄', '周', '吴']
    last_names = ['伟', '芳', '娜', '敏', '静', '强', '磊', '军', '洋', '勇']
    return random.choice(first_names) + random.choice(last_names)

def random_id_number():
    return ''.join([str(random.randint(0, 9)) for _ in range(18)])

def random_phone():
    return '1' + ''.join([str(random.randint(0, 9)) for _ in range(10)])

def random_email():
    domains = ['qq.com', '163.com', 'gmail.com', 'sina.com']
    return f'user{random.randint(1000, 9999)}@{random.choice(domains)}'

def random_address():
    cities = ['北京', '上海', '广州', '深圳', '杭州']
    return f'{random.choice(cities)}市{random.randint(1, 100)}路{random.randint(1, 50)}号'

def random_date(start_year, end_year):
    start = datetime(start_year, 1, 1)
    end = datetime(end_year, 12, 31)
    delta = end - start
    return start + timedelta(days=random.randint(0, delta.days))

def random_text(max_length=50):
    words = ['交易', '记录', '正常', '处理中', '完成']
    return ' '.join(random.choices(words, k=random.randint(3, 5)))[:max_length]

# 生成客户信息表 (customer_info)
def generate_customer_info(n=100):
    data = {
        'customer_id': [f'C{str(i).zfill(6)}' for i in range(1, n+1)],
        'customer_name': [random_name() for _ in range(n)],
        'gender': [random.choice(['男', '女']) for _ in range(n)],
        'birth_date': [random_date(1945, 2007) for _ in range(n)],
        'id_number': [random_id_number() for _ in range(n)],
        'phone_number': [random_phone() for _ in range(n)],
        'email': [random_email() for _ in range(n)],
        'address': [random_address() for _ in range(n)],
        'occupation': [random.choice(['教师', '工程师', '医生', '销售', '经理']) for _ in range(n)],
        'annual_income': [round(random.uniform(50000, 1000000), 2) for _ in range(n)],
        'credit_score': [random.randint(300, 850) for _ in range(n)],
        'marital_status': [random.choice(['已婚', '未婚', '离异']) for _ in range(n)],
        'education_level': [random.choice(['高中', '本科', '硕士', '博士']) for _ in range(n)],
        'registration_date': [random_date(2018, 2025) for _ in range(n)],
        'last_login_date': [random_date(2024, 2025) for _ in range(n)],
        'risk_tolerance': [random.choice(['低', '中', '高']) for _ in range(n)],
        'investment_goal': [random.choice(['短期收益', '长期增长', '资产保值']) for _ in range(n)],
        'customer_segment': [random.choice(['普通客户', 'VIP', '企业客户']) for _ in range(n)],
        'preferred_currency': [random.choice(['CNY', 'USD', 'EUR']) for _ in range(n)],
        'account_manager': [random_name() for _ in range(n)],
        'branch_code': [f'BR{str(random.randint(1, 50)).zfill(3)}' for _ in range(n)],
        'kyc_status': [random.choice(['已验证', '待验证']) for _ in range(n)],
        'tax_id': [random_id_number() for _ in range(n)],
        'nationality': [random.choice(['中国', '美国', '日本', '德国']) for _ in range(n)],
        'language_preference': [random.choice(['中文', '英文']) for _ in range(n)],
        'customer_status': [random.choice(['活跃', '休眠']) for _ in range(n)],
        'credit_limit': [round(random.uniform(10000, 500000), 2) for _ in range(n)],
        'referral_code': [f'REF{random.randint(1000, 9999)}' for _ in range(n)],
        'last_updated': [random_date(2024, 2025) for _ in range(n)],
        'notes': [random_text() for _ in range(n)]
    }
    return pd.DataFrame(data)

# 生成账户表 (account_info)
def generate_account_info(customer_df, n=150):
    data = {
        'account_id': [f'AC{str(i).zfill(6)}' for i in range(1, n+1)],
        'customer_id': [random.choice(customer_df['customer_id']) for _ in range(n)],
        'account_type': [random.choice(['储蓄账户', '投资账户', '信用卡']) for _ in range(n)],
        'account_status': [random.choice(['正常', '冻结', '关闭']) for _ in range(n)],
        'open_date': [random_date(2020, 2025) for _ in range(n)],
        'balance': [round(random.uniform(1000, 1000000), 2) for _ in range(n)],
        'currency': [random.choice(['CNY', 'USD', 'EUR']) for _ in range(n)],
        'interest_rate': [round(random.uniform(0.01, 5.0), 2) for _ in range(n)],
        'credit_line': [round(random.uniform(5000, 200000), 2) for _ in range(n)],
        'account_number': [f'ACC{random.randint(10000000, 99999999)}' for _ in range(n)],
        'branch_code': [f'BR{str(random.randint(1, 50)).zfill(3)}' for _ in range(n)],
        'last_transaction_date': [random_date(2024, 2025) for _ in range(n)],
        'monthly_fee': [round(random.uniform(0, 100), 2) for _ in range(n)],
        'overdraft_limit': [round(random.uniform(0, 50000), 2) for _ in range(n)],
        'payment_due_date': [random_date(2025, 2025) for _ in range(n)],
        'minimum_balance': [round(random.uniform(100, 10000), 2) for _ in range(n)],
        'auto_renewal': [random.choice(['是', '否']) for _ in range(n)],
        'linked_card': [f'CARD{random.randint(1000000000, 9999999999)}' for _ in range(n)],
        'account_tier': [random.choice(['基础', '高级', '白金']) for _ in range(n)],
        'investment_type': [random.choice(['股票', '基金', '债券', '混合']) for _ in range(n)],
        'maturity_date': [random_date(2026, 2030) for _ in range(n)],
        'transaction_limit': [round(random.uniform(1000, 100000), 2) for _ in range(n)],
        'online_banking': [random.choice(['启用', '禁用']) for _ in range(n)],
        'card_status': [random.choice(['有效', '过期', '挂失']) for _ in range(n)],
        'reward_points': [random.randint(0, 10000) for _ in range(n)],
        'statement_date': [str(random.randint(1, 28)) for _ in range(n)],
        'account_rating': [random.choice(['A', 'B', 'C']) for _ in range(n)],
        'promotion_code': [f'PROMO{random.randint(100, 999)}' for _ in range(n)],
        'last_updated': [random_date(2024, 2025) for _ in range(n)],
        'notes': [random_text() for _ in range(n)]
    }
    return pd.DataFrame(data)

# 生成交易表 (transaction_info)
def generate_transaction_info(account_df, n=500):
    data = {
        'transaction_id': [f'TX{str(i).zfill(6)}' for i in range(1, n+1)],
        'account_id': [random.choice(account_df['account_id']) for _ in range(n)],
        'transaction_date': [random_date(2024, 2025) for _ in range(n)],
        'transaction_type': [random.choice(['存款', '取款', '转账', '消费']) for _ in range(n)],
        'amount': [round(random.uniform(100, 50000), 2) for _ in range(n)],
        'currency': [random.choice(['CNY', 'USD', 'EUR']) for _ in range(n)],
        'merchant_name': [random_name() + '商店' for _ in range(n)],
        'merchant_category': [random.choice(['餐饮', '购物', '交通', '娱乐']) for _ in range(n)],
        'transaction_status': [random.choice(['成功', '待处理', '失败']) for _ in range(n)],
        'payment_method': [random.choice(['信用卡', '借记卡', '现金', '电子支付']) for _ in range(n)],
        'transaction_fee': [round(random.uniform(0, 50), 2) for _ in range(n)],
        'exchange_rate': [round(random.uniform(1.0, 7.5), 4) for _ in range(n)],
        'source_account': [random.choice(account_df['account_number']) for _ in range(n)],
        'destination_account': [random.choice(account_df['account_number']) for _ in range(n)],
        'transaction_time': [f'{random.randint(0, 23):02}:{random.randint(0, 59):02}:{random.randint(0, 59):02}' for _ in range(n)],
        'location': [random.choice(['北京', '上海', '广州', '深圳']) for _ in range(n)],
        'ip_address': [f'192.168.{random.randint(0, 255)}.{random.randint(0, 255)}' for _ in range(n)],
        'device_id': [f'DEV{random.randint(10000, 99999)}' for _ in range(n)],
        'fraud_flag': [random.choice(['是', '否']) for _ in range(n)],
        'approval_code': [f'AP{random.randint(100000, 999999)}' for _ in range(n)],
        'reference_number': [f'REF{random.randint(10000000, 99999999)}' for _ in range(n)],
        'category_code': [f'MCC{random.randint(1000, 9999)}' for _ in range(n)],
        'loyalty_points': [random.randint(0, 500) for _ in range(n)],
        'dispute_status': [random.choice(['无', '待处理', '已解决']) for _ in range(n)],
        'channel': [random.choice(['线上', '线下', 'ATM']) for _ in range(n)],
        'billing_cycle': [random.choice(['1月', '2月', '3月', '4月', '5月', '6月', '7月', '8月', '9月', '10月', '11月', '12月']) for _ in range(n)],
        'card_number': [f'CARD{random.randint(1000000000, 9999999999)}' for _ in range(n)],
        'transaction_description': [random_text() for _ in range(n)],
        'last_updated': [random_date(2024, 2025) for _ in range(n)],
        'notes': [random_text() for _ in range(n)]
    }
    return pd.DataFrame(data)


# 生成数据并保存为Excel文件
customer_df = generate_customer_info(100)
account_df = generate_account_info(customer_df, 150)
transaction_df = generate_transaction_info(account_df, 500)

# 使用 pd.to_excel 保存到单独文件
customer_df.to_excel('customer_info.xlsx', index=False)
account_df.to_excel('account_info.xlsx', index=False)
transaction_df.to_excel('transaction_info.xlsx', index=False)
